

-- Table: sys_sql

-- DROP TABLE sys_sql;

CREATE TABLE sys_sql
(
    id varchar(32)  NOT NULL,
    sql text  NOT NULL,
    result_type integer NOT NULL DEFAULT 1,
    description text ,
    CONSTRAINT sys_sql_pkey PRIMARY KEY (id)
);

COMMENT ON COLUMN sys_sql.result_type IS '1-列表,2-单数据';




-- function 

-- FUNCTION: execute_sql(character varying, anyarray)

-- DROP FUNCTION execute_sql(character varying, anyarray);

CREATE OR REPLACE FUNCTION execute_sql( id_ varchar, params_ anyarray)
    RETURNS json
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE 
	_sql_row  sys_sql%ROWTYPE ; -- sql 查询语句 
	_n_sql 		text ; 
	_result		json ; 
BEGIN
 
	select * into _sql_row from sys_sql where id = id_  ; 
	if _sql_row is NULL then  
		return json_build_object('code' , 404, 'error','找不到对应查询语句'); 
	end if ; 
	 _n_sql = ' with t_tmp as (' || _sql_row.sql || ') ';
 
	-- 判断返回类型

	if _sql_row.result_type = 1 THEN 
		-- list 
		_n_sql = _n_sql ||  ' select json_agg(row_to_json(t)) from t_tmp as t ' ; 
	else  
		--only one 
		_n_sql = _n_sql ||  ' select row_to_json(t)  from t_tmp as t limit 1' ; 
	end if ; 

	-- raise notice 'sql:%' , _n_sql; 
	execute _n_sql using params_  into _result ; 
	return  json_build_object('code' , 0 , 'data' , _result);  
END;
$BODY$; 


 